package com.reservation.mapper;

import com.reservation.entity.Reservation;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

/**
 * 预订数据访问接口
 */
@Mapper
@Repository
public interface ReservationMapper {

    /**
     * 根据ID查询预订
     */
    @Select("SELECT * FROM reservations WHERE id = #{id}")
    Reservation selectById(Long id);

    /**
     * 根据订单号查询预订
     */
    @Select("SELECT * FROM reservations WHERE order_number = #{orderNumber}")
    Reservation selectByOrderNumber(String orderNumber);

    /**
     * 查询指定日期的所有预订
     */
    @Select("SELECT * FROM reservations WHERE reservation_date = #{date} ORDER BY reservation_time ASC")
    List<Reservation> selectByDate(LocalDate date);

    /**
     * 查询指定用户的所有预订
     */
    @Select("SELECT * FROM reservations WHERE user_id = #{userId} ORDER BY created_at DESC")
    List<Reservation> selectByUserId(Long userId);

    /**
     * 查询指定状态的预订
     */
    @Select("SELECT * FROM reservations WHERE status = #{status} ORDER BY created_at DESC")
    List<Reservation> selectByStatus(String status);

    /**
     * 统计指定日期的预订人数
     */
    @Select("SELECT SUM(person_count) FROM reservations WHERE reservation_date = #{date} AND status != 'CANCELLED'")
    Integer countPersonsByDate(LocalDate date);

    /**
     * 查询日期范围内的预订
     */
    @Select("SELECT * FROM reservations WHERE reservation_date BETWEEN #{startDate} AND #{endDate} ORDER BY reservation_date ASC, reservation_time ASC")
    List<Reservation> selectByDateRange(LocalDate startDate, LocalDate endDate);

    /**
     * 查询所有预订
     */
    @Select("SELECT * FROM reservations ORDER BY created_at DESC")
    List<Reservation> selectAll();

    /**
     * 根据用户ID和状态查找预订记录
     */
    @Select("SELECT * FROM reservations WHERE user_id = #{userId} AND status = #{status} ORDER BY created_at DESC")
    List<Reservation> selectByUserIdAndStatus(@Param("userId") Long userId, @Param("status") String status);

    /**
     * 根据商品ID查找预订记录
     */
    @Select("SELECT * FROM reservations WHERE product_id = #{productId} ORDER BY created_at DESC")
    List<Reservation> selectByProductId(Long productId);

    /**
     * 根据联系电话查找预订记录
     */
    @Select("SELECT * FROM reservations WHERE contact_phone = #{contactPhone} ORDER BY created_at DESC")
    List<Reservation> selectByContactPhone(String contactPhone);

    /**
     * 统计用户的预订数量
     */
    @Select("SELECT COUNT(*) FROM reservations WHERE user_id = #{userId}")
    Long countByUserId(Long userId);

    /**
     * 统计指定状态的预订数量
     */
    @Select("SELECT COUNT(*) FROM reservations WHERE status = #{status}")
    Long countByStatus(String status);

    /**
     * 统计用户指定状态的预订数量
     */
    @Select("SELECT COUNT(*) FROM reservations WHERE user_id = #{userId} AND status = #{status}")
    Long countByUserIdAndStatus(@Param("userId") Long userId, @Param("status") String status);

    /**
     * 查找指定时间范围内的预订记录
     */
    @Select("SELECT * FROM reservations WHERE created_at BETWEEN #{startTime} AND #{endTime} ORDER BY created_at DESC")
    List<Reservation> selectByCreatedAtBetween(@Param("startTime") LocalDateTime startTime,
                                             @Param("endTime") LocalDateTime endTime);

    /**
     * 查找超时未确认的预订记录
     */
    @Select("SELECT * FROM reservations WHERE status = 'PENDING' AND created_at < #{expireTime}")
    List<Reservation> selectExpiredPendingReservations(@Param("expireTime") LocalDateTime expireTime);

    /**
     * 插入预订
     */
    @Insert("INSERT INTO reservations(reservation_date, reservation_time, person_count, contact_name, contact_phone, " +
           "remark, total_amount, status, order_number, payment_status, payment_time, payment_method, user_id, product_id, created_at, updated_at) " +
           "VALUES(#{reservationDate}, #{reservationTime}, #{personCount}, #{contactName}, #{contactPhone}, #{remark}, " +
           "#{totalAmount}, #{status}, #{orderNumber}, #{paymentStatus}, #{paymentTime}, #{paymentMethod}, #{userId}, #{productId}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Reservation reservation);

    /**
     * 更新预订
     */
    @Update("UPDATE reservations SET reservation_date=#{reservationDate}, reservation_time=#{reservationTime}, " +
           "person_count=#{personCount}, contact_name=#{contactName}, contact_phone=#{contactPhone}, " +
           "remark=#{remark}, total_amount=#{totalAmount}, status=#{status}, order_number=#{orderNumber}, " +
           "payment_status=#{paymentStatus}, payment_time=#{paymentTime}, payment_method=#{paymentMethod}, " +
           "user_id=#{userId}, product_id=#{productId}, updated_at=NOW() " +
           "WHERE id=#{id}")
    int update(Reservation reservation);

    /**
     * 更新预订状态
     */
    @Update("UPDATE reservations SET status=#{status}, updated_at=NOW() WHERE id=#{id}")
    int updateStatus(@Param("id") Long id, @Param("status") String status);

    /**
     * 更新支付状态
     */
    @Update("UPDATE reservations SET payment_status=#{paymentStatus}, payment_time=NOW(), updated_at=NOW() WHERE id=#{id}")
    int updatePaymentStatus(@Param("id") Long id, @Param("paymentStatus") String paymentStatus);

    /**
     * 删除预订
     */
    @Delete("DELETE FROM reservations WHERE id=#{id}")
    int deleteById(Long id);
}
